package com.java.dao.imp;

import com.java.bean.Courier;
import com.java.dao.BaseCourierDao;
import com.java.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BaseCourierDaoMysql implements BaseCourierDao {
    Date date = new Date();

    //用于查询数据库中的全部快递员（总数+新增）
    public static final String SQL_CONSOLE = "SELECT COUNT(ID) courier_size,COUNT(TO_DAYS(CREATETIME)=TO_DAYS(NOW()) OR NULL) courier_day FROM COURIER";

    //用于查询数据库中的所有快递员信息
    public static final String SQL_FIND_ALL = "SELECT * FROM COURIER";
    //用于分页查询数据库中的快递员信息
    public static final String SQL_FIND_LIMIT = "SELECT * FROM COURIER LIMIT ?,?";
    //录入快递员
    public static final String SQL_INSERT = "INSERT INTO COURIER (NAME,PHONE,ID_CARD,PASSWORD,numberOfPackages,CREATETIME,LOGINTIME) VALUES(?,?,?,?,?,?,?)";
    //快递员修改
    public static final String SQL_UPDATE_BY_PHONE = "UPDATE COURIER SET NAME=?,PHONE=?,ID_CARD=?,password=? WHERE phone=?";
    //快递员的删除
    public static final String SQL_DELETE = "DELETE FROM COURIER WHERE PHONE=?";
    //快递员的查询
    public static final String SQL_FIND_BY_PHONE = "SELECT * FROM COURIER WHERE PHONE=?";

    /**
     * 用于查询数据库中的全部快递员（总数+新增）
     *
     * @return [{size:总数,day:新增}
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_CONSOLE);
            //3.    填充参数(可选)
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            if(result.next()){
                int courier_size = result.getInt("courier_size");
                int courier_day = result.getInt("courier_day");

                Map data1 = new HashMap();
                data1.put("courier_size",courier_size);
                data1.put("courier_day",courier_day);
                data.add(data1);
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }

        return data;
    }

    /**
     * 用于查询所有快递员
     *
     * @param limit      是否分页的标记，true表示分页。false表示查询所有快递员
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 快递员的集合
     */
    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNumber) {
        ArrayList<Courier> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            if(limit) {
                state = conn.prepareStatement(SQL_FIND_LIMIT);
                //3.    填充参数(可选)
                state.setInt(1,offset);
                state.setInt(2,pageNumber);
            }else {
                state = conn.prepareStatement(SQL_FIND_ALL);
            }

            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while(result.next()){
                int id = result.getInt("id");
                String name = result.getString("name");
                String phone = result.getString("phone");
                String ID_Card = result.getString("ID_Card");
                String password = result.getString("password");
                int numberOfPackages = Integer.parseInt(result.getString("numberOfPackages"));
                Timestamp createTime = result.getTimestamp("createTime");
                Timestamp loginTime = result.getTimestamp("loginTime");
                Courier e = new Courier(id,name,phone,ID_Card,password,numberOfPackages,createTime,loginTime);
                data.add(e);
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return data;
    }

    /**
     * 快递员的修改
     * @param phone 要修改的快递员的手机号
     * @param newCourier 新的快递员对象（name，phone,ID_Card,password）
     * @return 修改的结果，true表示成功，false表示失败
     */
    @Override
    public boolean update(String phone,Courier newCourier) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE_BY_PHONE);
            state.setString(1,newCourier.getName());
            state.setString(2,phone);
            state.setString(3,newCourier.getID_Card());
            state.setString(4,newCourier.getPassword());
            state.setString(5,newCourier.getPhone());
            int i = state.executeUpdate();
            return i > 0 ? true : false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 快递员的录入
     *
     * @param c 要录入的快递员
     * @return 录入的结果，true表示成功，false表示失败
     */
    @Override
    public boolean insert(Courier c){
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            state = conn.prepareStatement(SQL_INSERT);
            //3.    填充参数
            state.setString(1,c.getName());
            state.setString(2,c.getPhone());
            state.setString(3,c.getID_Card());
            state.setString(4,c.getPassword());
            state.setString(5,"0");
            state.setDate(6, new java.sql.Date(date.getTime()));
            state.setDate(7, new java.sql.Date(date.getTime()));
            //4.    执行SQL语句,并获取执行结果
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据phone，删除单个快递员信息
     * @param phone 要删除的快递员手机号
     * @return 删除的结果，true表示成功，false表示失败
     */
    @Override
    public boolean delete(String phone) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_DELETE);
            state.setString(1,phone);
            return state.executeUpdate()>0?true:false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据phone，查询快递员信息
     *
     * @param phone 单号
     * @return 查询的快递信息，单号不存在时返回null
     */
    @Override
    public Courier findByNumber(String phone) {
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_FIND_BY_PHONE);
            //3.    填充参数(可选)
            state.setString(1,phone);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            if(result.next()){
                int id = result.getInt("id");
                String name = result.getString("name");
                String ID_Card = result.getString("ID_Card");
                String password = result.getString("password");
                int numberOfPackages = Integer.parseInt(result.getString("numberOfPackages"));
                Timestamp createTime = result.getTimestamp("createTime");
                Timestamp loginTime = result.getTimestamp("loginTime");
                Courier c = new Courier(id,name,phone,ID_Card,password,numberOfPackages,createTime,loginTime);
                return c;
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return null;
    }
}
